package org.misalen.web.database.repository.mysql;

import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.misalen.utils.TextUtil;
import org.misalen.web.database.dao.mysql.impl.BaseMySqlDaoImpl;
import org.misalen.web.database.model.mysql.pro.ProWebAdvertising;
import org.misalen.web.database.util.PaginationFrom;
import org.springframework.stereotype.Repository;

@Repository
public class ProWebAdvertisingRepository extends BaseMySqlDaoImpl<ProWebAdvertising> {

	@Override
	public PaginationFrom<ProWebAdvertising> findPageByBean(ProWebAdvertising allclass,
			PaginationFrom<ProWebAdvertising> pagination) {
		Session session = this.openSession();
		String where = getHqlWhere(allclass);
		String sql = "select a.*,u.userName as userName,CONVERT(SUM(CASE WHEN r.userId is NOT NULL THEN 1 ELSE 0 END),CHAR) AS shareNum from ProWebAdvertising a left join ProUser u on a.userId=u.primaryKey "
				+ " LEFT JOIN ProWebReward r ON a.primaryKey=r.webId " + where
				+ " group by a.primaryKey order by addTime desc";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(new AliasToBeanResultTransformer(allclass.getClass()));
		pagination.setTotal(Long.valueOf(session.createSQLQuery(
				"select count(*) from ProWebAdvertising a left join ProUser u on a.userId=u.primaryKey " + where)
				.uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProWebAdvertising> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}

	public PaginationFrom<ProWebAdvertising> findByVicinity(String userId, BigDecimal latitude, BigDecimal longitude,
			PaginationFrom<ProWebAdvertising> pagination) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append(
				"SELECT A.primaryKey,A.adverSlogan,A.adverImagesUrl,A.promotionExpenses,A.grabThe,CONVERT(sum(CASE WHEN B.userId = '");
		selectBuffer.append(userId);
		selectBuffer.append("' THEN 1 ELSE NULL END),CHAR) isGet,CONVERT(sum(CASE WHEN B.userId = '");
		selectBuffer.append(userId);
		selectBuffer.append("' THEN B.money ELSE NULL END),SIGNED) getMoney");
		selectBuffer.append(" FROM ProWebAdvertising A LEFT JOIN ProWebReward B ON A.primaryKey = B.webId");
		selectBuffer.append(" WHERE promotionMode='near' and A.state = 'generalize' ");
		selectBuffer
				.append(" and sqrt((((?0-longitude)*PI()*12656*cos(((?1+latitude)/promotionDistance/1000)*PI()/180)/180) "
						+ " * ((?0-longitude)*PI()*12656*cos (((?1+latitude)/promotionDistance/1000)*PI()/180)/180) "
						+ " ) + ( ((?1-latitude)*PI()*12656/180) * ((?1-latitude)*PI()*12656/180)   )  "
						+ " )<promotionDistance/1000 ");
		selectBuffer.append(" GROUP BY A.primaryKey ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setParameter("0", longitude);
		query.setParameter("1", latitude);
		query.setResultTransformer(new AliasToBeanResultTransformer(ProWebAdvertising.class));
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append("select count(*) from (" + selectBuffer + ") T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).setParameter("0", longitude)
				.setParameter("1", latitude).uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProWebAdvertising> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}

	public PaginationFrom<ProWebAdvertising> findByAreaa(String userId, String belongCity,
			PaginationFrom<ProWebAdvertising> pagination) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append(
				"SELECT A.primaryKey,A.adverSlogan,A.adverImagesUrl,A.promotionExpenses,A.grabThe,CONVERT(sum(CASE WHEN B.userId = '");
		selectBuffer.append(userId);
		selectBuffer.append("' THEN 1 ELSE NULL END),CHAR) isGet,CONVERT(sum(CASE WHEN B.userId = '");
		selectBuffer.append(userId);
		selectBuffer.append("' THEN B.money ELSE NULL END),SIGNED) getMoney");
		selectBuffer.append(" FROM ProWebAdvertising A LEFT JOIN ProWebReward B ON A.primaryKey = B.webId");
		selectBuffer.append(" WHERE promotionMode='area' and A.state = 'generalize' ");
		selectBuffer.append(" and (A.promotionCity = '" + belongCity + "' or A.promotionCity = '全国') ");
		selectBuffer.append(" GROUP BY A.primaryKey ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setResultTransformer(new AliasToBeanResultTransformer(ProWebAdvertising.class));
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append("select count(*) from (" + selectBuffer + ") T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProWebAdvertising> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}

	public PaginationFrom<ProWebAdvertising> findByUser(String userId, String state,
			PaginationFrom<ProWebAdvertising> pagination) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append("SELECT A.primaryKey,A.addTime,A.adverSlogan,A.adverImagesUrl,A.promotionExpenses,A.promotionNumber");
		selectBuffer.append(" FROM ProWebAdvertising A ");
		selectBuffer.append(" WHERE A.state = '" + state + "' ");
		selectBuffer.append(" AND A.userId = '" + userId + "' ");
		selectBuffer.append(" ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setResultTransformer(new AliasToBeanResultTransformer(ProWebAdvertising.class));
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append("select count(*) from (" + selectBuffer + ") T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProWebAdvertising> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}

	public ProWebAdvertising getValid(String webId) {
		String hql = "from ProWebAdvertising where primaryKey=?0 and grabThe=?1";
		return get(hql, new Object[] { webId, TextUtil.booleanToString(false) });
	}

	public void updateGrabThe(String grabThe, String webId) {
		String sql = "update ProWebAdvertising set grabThe='" + grabThe + "' where primaryKey='" + webId + "'";
		Session session = this.openSession();
		Query query = session.createQuery(sql);
		query.executeUpdate();
		closeSession(session);
	}

}